﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

namespace DataAccess.SqlServer
{
    public class SqlServerGiangVienThinhGiang: IGiangVienThinhGiang
    {
        public GiangVienThinhGiang GetGiangVienThinhGiang(int oid)
        {
            string sql = @" Select b.MaQuanLy, b.Ho, b.Ten, b.GioiTinh, b.NgaySinh, b.BoPhan, b.NoiSinh, b.QueQuan, b.DiaChiThuongTru,
                                    b.NoiOHienNay, b.DienThoaiDiDong, b.DienThoaiCoQuan, b.Email, b.HopDong, b.TinhTrang, b.GhiChu,
                                    a.CoQuanCongTac, a.TaiLieuGiangDay
                            From GiangVienThinhGiang a inner join HoSo b on a.Oid = b.Oid
                            Where a.Oid = @oid";

            SqlParameter param = new SqlParameter("@oid", oid);
            DataRow row = DataProvider.GetDataRow(sql, param);

            GiangVienThinhGiang giangVienThinhGiang = new GiangVienThinhGiang();

            giangVienThinhGiang.Oid = oid;
            giangVienThinhGiang.MaQuanLy = row["MaQuanLy"].ToString();
            giangVienThinhGiang.Ho = row["Ho"].ToString();
            giangVienThinhGiang.Ten = row["Ten"].ToString();
            byte gioiTinh;
            if (byte.TryParse(row["GioiTinh"].ToString(), out gioiTinh))
                giangVienThinhGiang.GioiTinh = (EGioiTinh)gioiTinh;
            DateTime ngaySinh;
            if (DateTime.TryParse(row["NgaySinh"].ToString(), out ngaySinh))
                giangVienThinhGiang.NgaySinh = ngaySinh;
            
            int id;
            if (int.TryParse(row["BoPhan"].ToString(), out id))
            {
                SqlServerBoPhan sqlBoPhan = new SqlServerBoPhan();
                giangVienThinhGiang.BoPhan = sqlBoPhan.GetBoPhan(id);
            }
            giangVienThinhGiang.NoiSinh = row["NoiSinh"].ToString();
            giangVienThinhGiang.QueQuan = row["QueQuan"].ToString();
            giangVienThinhGiang.DiaChiThuongTru = row["DiaChiThuongTru"].ToString();
            giangVienThinhGiang.NoiOHienNay = row["NoiOHienNay"].ToString();
            giangVienThinhGiang.DienThoaiDiDong = row["DienThoaiDiDong"].ToString();
            giangVienThinhGiang.DienThoaiCoQuan = row["DienThoaiCoQuan"].ToString();
            giangVienThinhGiang.Email = row["Email"].ToString();

            if (int.TryParse(row["HopDong"].ToString(), out id))
            {
                //SqlServerHopDongThinhGiang sqlServerHopDongThinhGiang = new SqlServerHopDongThinhGiang();
                giangVienThinhGiang.HopDong = HopDongFactory.GetHopDong(id);
            }
            if (int.TryParse(row["TinhTrang"].ToString(), out id))
            {
                SqlServerTinhTrang sqlServerTinhTrang = new SqlServerTinhTrang();
                giangVienThinhGiang.TinhTrang = sqlServerTinhTrang.GetTinhTrang(id);
            }
            giangVienThinhGiang.GhiChu = row["GhiChu"].ToString();


            return giangVienThinhGiang;
        }

        public List<GiangVienThinhGiang> GetListGiangVienThinhGiang()
        {
            string sql = @" Select b.Oid, b.MaQuanLy, b.Ho, b.Ten, b.GioiTinh, b.NgaySinh, b.BoPhan, b.NoiSinh, b.QueQuan, b.DiaChiThuongTru,
                                    b.NoiOHienNay, b.DienThoaiDiDong, b.DienThoaiCoQuan, b.Email, b.HopDong, b.TinhTrang, b.GhiChu,
                                    a.CoQuanCongTac, a.TaiLieuGiangDay
                            From GiangVienThinhGiang a inner join HoSo b on a.Oid = b.Oid";

            DataTable dt = DataProvider.GetDataTable(sql);
            List<GiangVienThinhGiang> list = new List<GiangVienThinhGiang>();

            foreach (DataRow row in dt.Rows)
            {
                GiangVienThinhGiang giangVienThinhGiang = new GiangVienThinhGiang();

                giangVienThinhGiang.Oid = int.Parse(row["Oid"].ToString());
                giangVienThinhGiang.MaQuanLy = row["MaQuanLy"].ToString();
                giangVienThinhGiang.Ho = row["Ho"].ToString();
                giangVienThinhGiang.Ten = row["Ten"].ToString();
                byte gioiTinh;
                if (byte.TryParse(row["GioiTinh"].ToString(), out gioiTinh))
                    giangVienThinhGiang.GioiTinh = (EGioiTinh)gioiTinh;
                DateTime ngaySinh;
                if (DateTime.TryParse(row["NgaySinh"].ToString(), out ngaySinh))
                    giangVienThinhGiang.NgaySinh = ngaySinh;

                int id;
                if (int.TryParse(row["BoPhan"].ToString(), out id))
                {
                    SqlServerBoPhan sqlBoPhan = new SqlServerBoPhan();
                    giangVienThinhGiang.BoPhan = sqlBoPhan.GetBoPhan(id);
                }
                giangVienThinhGiang.NoiSinh = row["NoiSinh"].ToString();
                giangVienThinhGiang.QueQuan = row["QueQuan"].ToString();
                giangVienThinhGiang.DiaChiThuongTru = row["DiaChiThuongTru"].ToString();
                giangVienThinhGiang.NoiOHienNay = row["NoiOHienNay"].ToString();
                giangVienThinhGiang.DienThoaiDiDong = row["DienThoaiDiDong"].ToString();
                giangVienThinhGiang.DienThoaiCoQuan = row["DienThoaiCoQuan"].ToString();
                giangVienThinhGiang.Email = row["Email"].ToString();

                if (int.TryParse(row["HopDong"].ToString(), out id))
                {
                    //SqlServerHopDongThinhGiang sqlServerHopDongThinhGiang = new SqlServerHopDongThinhGiang();
                    giangVienThinhGiang.HopDong = HopDongFactory.GetHopDong(id);
                }
                if (int.TryParse(row["TinhTrang"].ToString(), out id))
                {
                    SqlServerTinhTrang sqlServerTinhTrang = new SqlServerTinhTrang();
                    giangVienThinhGiang.TinhTrang = sqlServerTinhTrang.GetTinhTrang(id);
                }
                giangVienThinhGiang.GhiChu = row["GhiChu"].ToString();

                list.Add(giangVienThinhGiang);
            }
            return list;
        }

        public List<GiangVienThinhGiang> GetListGiangVienThinhGiang(BoPhan boPhan)
        {
            string sql = @" Select b.Oid, b.MaQuanLy, b.Ho, b.Ten, b.GioiTinh, b.NgaySinh, b.BoPhan, b.NoiSinh, b.QueQuan, b.DiaChiThuongTru,
                                    b.NoiOHienNay, b.DienThoaiDiDong, b.DienThoaiCoQuan, b.Email, b.HopDong, b.TinhTrang, b.GhiChu,
                                    a.CoQuanCongTac, a.TaiLieuGiangDay
                            From GiangVienThinhGiang a inner join HoSo b on a.Oid = b.Oid
                            Where BoPhan = @BoPhan";

            SqlParameter param = new SqlParameter("@BoPhan", boPhan.Oid);

            DataTable dt = DataProvider.GetDataTable(sql,param);

            List<GiangVienThinhGiang> list = new List<GiangVienThinhGiang>();

            foreach (DataRow row in dt.Rows)
            {
                GiangVienThinhGiang giangVienThinhGiang = new GiangVienThinhGiang();

                giangVienThinhGiang.Oid = int.Parse(row["Oid"].ToString());
                giangVienThinhGiang.MaQuanLy = row["MaQuanLy"].ToString();
                giangVienThinhGiang.Ho = row["Ho"].ToString();
                giangVienThinhGiang.Ten = row["Ten"].ToString();
                byte gioiTinh;
                if (byte.TryParse(row["GioiTinh"].ToString(), out gioiTinh))
                    giangVienThinhGiang.GioiTinh = (EGioiTinh)gioiTinh;
                DateTime ngaySinh;
                if (DateTime.TryParse(row["NgaySinh"].ToString(), out ngaySinh))
                    giangVienThinhGiang.NgaySinh = ngaySinh;

                int id;
                if (int.TryParse(row["BoPhan"].ToString(), out id))
                {
                    SqlServerBoPhan sqlBoPhan = new SqlServerBoPhan();
                    giangVienThinhGiang.BoPhan = sqlBoPhan.GetBoPhan(id);
                }
                giangVienThinhGiang.NoiSinh = row["NoiSinh"].ToString();
                giangVienThinhGiang.QueQuan = row["QueQuan"].ToString();
                giangVienThinhGiang.DiaChiThuongTru = row["DiaChiThuongTru"].ToString();
                giangVienThinhGiang.NoiOHienNay = row["NoiOHienNay"].ToString();
                giangVienThinhGiang.DienThoaiDiDong = row["DienThoaiDiDong"].ToString();
                giangVienThinhGiang.DienThoaiCoQuan = row["DienThoaiCoQuan"].ToString();
                giangVienThinhGiang.Email = row["Email"].ToString();

                if (int.TryParse(row["HopDong"].ToString(), out id))
                {
                    //SqlServerHopDongThinhGiang sqlServerHopDongThinhGiang = new SqlServerHopDongThinhGiang();
                    giangVienThinhGiang.HopDong = HopDongFactory.GetHopDong(id);
                }
                if (int.TryParse(row["TinhTrang"].ToString(), out id))
                {
                    SqlServerTinhTrang sqlServerTinhTrang = new SqlServerTinhTrang();
                    giangVienThinhGiang.TinhTrang = sqlServerTinhTrang.GetTinhTrang(id);
                }
                giangVienThinhGiang.GhiChu = row["GhiChu"].ToString();

            }
            return list;
        }

        public int Insert(GiangVienThinhGiang obj)
        {
            SqlServerHoSo sqlServerHoSo = new SqlServerHoSo();
            int oid = sqlServerHoSo.Insert((HoSo)obj);
            if (oid > 0)
            {
                string sql = @" Insert into GiangVienThinhGiang(Oid, CoQuanCongTac, TaiLieuGiangDay)
                                Values(@Oid, @CoQuanCongTac, @TaiLieuGiangDay)";
                SqlParameter[] param = {
                                           new SqlParameter("@Oid", oid),
                                           new SqlParameter("@CoQuanCongTac", obj.CoQuanCongTac),
                                           new SqlParameter("@TaiLieuGiangDay", obj.TaiLieuGiangDay)
                                       };
                int temp = DataProvider.Insert(sql,false, param);

                if (oid > 0)
                {
                    ThamChieuDuLieu thamChieuDuLieu = new ThamChieuDuLieu();
                    thamChieuDuLieu.Cha = new SqlServerKieuDuLieu().GetKieuDuLieu(typeof(HoSo).ToString());
                    thamChieuDuLieu.Con = new SqlServerKieuDuLieu().GetKieuDuLieu(typeof(GiangVienThinhGiang).ToString());
                    thamChieuDuLieu.GiaTri = oid;

                    SqlServerThamChieuDuLieu sqlServerThamChieuDuLieu = new SqlServerThamChieuDuLieu();
                    sqlServerThamChieuDuLieu.Insert(thamChieuDuLieu);
                }
            }
            return oid;
        }

        public int Update(GiangVienThinhGiang obj)
        {
            SqlServerHoSo sqlServerHoSo = new SqlServerHoSo();
            int temp = sqlServerHoSo.Update((HoSo)obj);

            if (temp > 0)
            {
                string sql = @" Update GiangVienThinhGiang
                                Set CoQuanCongTac = @CoQuanCongTac,
                                    TaiLieuGiangDay = @TaiLieuGiangDay
                                Where Oid = @Oid";
                SqlParameter[] param = {
                                           new SqlParameter("@Oid", obj.Oid),
                                           new SqlParameter("@CoQuanCongTac", obj.CoQuanCongTac),
                                           new SqlParameter("@TaiLieuGiangDay", obj.TaiLieuGiangDay)
                                       };
                temp = DataProvider.Update(sql, param);
            }
            return temp;
        }

        public int Delete(GiangVienThinhGiang obj)
        {
            string sql = @" Delete From GiangVienThinhGiang Where Oid = @Oid";
            SqlParameter param = new SqlParameter("@Oid", obj.Oid);

            int temp = DataProvider.Update(sql, param);
            if (temp > 0)
            {
                SqlServerHoSo sqlServerHoSo = new SqlServerHoSo();
                temp = sqlServerHoSo.Delete((HoSo)obj);

                if (temp > 0)
                {
                    ThamChieuDuLieu thamChieuDuLieu = new ThamChieuDuLieu();
                    thamChieuDuLieu.GiaTri = obj.Oid;
                    SqlServerThamChieuDuLieu sqlServerThamChieuDuLieu = new SqlServerThamChieuDuLieu();
                    temp = sqlServerThamChieuDuLieu.Delete(thamChieuDuLieu);
                }
            }
            return temp;
        }


        public int Save(GiangVienThinhGiang obj)
        {
            throw new NotImplementedException();
        }
    }
}
